package cn.edu.buaa.cnsatm.traffic_big_data_server.highway.dao;

import cn.edu.buaa.cnsatm.traffic_big_data_server.highway.model.HighwayCongestionModel;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HighwayCongestionDAO {
    public static ResultSet getTopKCongestHighwayFromDatabase(Integer k){
        /**
         * @author: LX
         * @methodName: getTopKCongestHighwayFromDatabase
         * @description: 从离线数据库中获取离当前时间最近的，拥挤度index最高的k个公路，这个没有预报，不需要当前时间
         * @data: 2019-10-19

        /*只关注表内最新的5H内的数据*/
        String sql = "SELECT DISTINCT road_name, description, index, update_time, geom " +
                " FROM highway_congestion WHERE road_name LIKE '%高速' AND round(date_part('epoch', (SELECT max(update_time) FROM highway_congestion) - update_time)::NUMERIC / 60) < 60 * 5 ORDER BY index DESC LIMIT " + k.toString();
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static String insertHighwayCongestion(HighwayCongestionModel highwayCongestionModel, boolean isInsert) throws SQLException {
        /**
         * @author: LX
         * @methodName: insertHighwayCongestion
         * @description: 将海况想应得数据插入到highway_congestion表中
         * @data: 2019-10-19
         **/
        int insertNum = 0;

        String sql = "INSERT into highway_congestion (road_id, road_name, description, update_time, index, speed, city_name, links, longitude, latitude, geom) " +
                "values('" + highwayCongestionModel.getRoad_id() + "', '" + highwayCongestionModel.getRoad_name() + "', '"  +
                highwayCongestionModel.getDescription() + "','" + highwayCongestionModel.getUpdatetime() + "'," +
                highwayCongestionModel.getIndex() + "," + highwayCongestionModel.getSpeed() + ",'" + highwayCongestionModel.getCity_name() + "','" +
                highwayCongestionModel.getLinks() + "'," + highwayCongestionModel.getLongitude() + "," + highwayCongestionModel.getLatitude() + ",'" +
                highwayCongestionModel.getGeom() +"' )";
        //TODO:给表加一个geom
        if(isInsert == true){
            Statement stmt = null;
            try {
                stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
                insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
                //System.out.print(insertNum);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stmt.close();
            }
        }
        else return sql;
        return null;
    }
    public static ResultSet getAllMainHighWay(){
        /**
         * @author: LX
         * @methodName: getAllMainHighWay
         * @description: 获取所有高速主干路的名称
         * @data: 2019-11-05
        */

        String sql = "SELECT highway_name, highway_code FROM main_highway_cn group by highway_name, highway_code";
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getNearestHighwaySegment(String highwayName, Double longitude, Double latitude){
        /**
         * @author: LX
         * @methodName: getNearestHighwaySegment
         * @description: 获取与点坐标最近的高速主干路段
         * @data: 2019-11-05
         */
        String distance = "ST_Distance(" +
                "    ST_SetSRID(ST_MakePoint(" + longitude + ", " + latitude + "),4326)::geography," +
                "    ST_SetSRID(ST_MakePoint(x1, y1),4326)::geography" +
                ")";
        String sql = "SELECT geom FROM highway_class_freeway WHERE name = '" + highwayName + "' AND " + distance + " = (SELECT MIN(" + distance + ") FROM highway_class_freeway WHERE name = '" + highwayName + "')";
        //TODO:确认字段名
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static ResultSet getHighwayLocation(){
        /**
         * @author: LX
         * @methodName: getHighwayCongestionMap
         * @description: 获取最新的公路拥挤图，拥挤度小于等于ignoreValue不显示
         * @data: 2019-12-2
         */
        String sql = "SELECT distinct geom from main_highway_cn where geom is not null";
        //String sql = "SELECT geom FROM highway_class_freeway WHERE name = '" + highwayName + "' AND " + distance + " = (SELECT MIN(" + distance + ") FROM highway_class_freeway WHERE name = '" + highwayName + "')";
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getHighwayLocationByHighwayName(String highwayName){
        /**
         * @author: LX
         * @methodName: getHighwayLocationByHighwayName
         * @description: 根据名称获取高速公路位置
         * @data: 2019-12-3
         */
        String sql = "SELECT distinct geom from main_highway_cn where highway_name = '" + highwayName +"' and geom is not null";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getAllHighwaySegName(){
        /**
         * @author: LX
         * @methodName: getAllHighwaySegName
         * @description: 获取所有高速公路段名，如京哈高速-1
         * @data: 2019-12-2
         */
        String sql = "SELECT distinct on (seg_name, city_code) seg_name, city_code from main_highway_cn where seg_name is not null and city_code is not null";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static String updateHighwayCongestionMap(String segID, String update_time, double congestValue, String city_code) throws SQLException {
        /**
         * @author: LX
         * @methodName: updateHighwayCongestionMap
         * @description: 更新拥挤度
         * @data: 2019-12-3
         */
        String sql = "update main_highway_cn set congest = " + congestValue + ", update_time = '" +
                update_time + "' where seg_id = '" + segID + "' and city_code = '" + city_code + "';\n";
//        Statement stmt = null;
//        try {
//            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
//            //insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
//            stmt.executeUpdate(sql);
//            //System.out.print(insertNum);
//        } catch (SQLException e) {
//            System.out.println(sql);
//            e.printStackTrace();
//        } finally {
//            stmt.close();
//        }
        return sql;
    }
    public static ResultSet getHighwayCongestionMap(double ignoreValue){
        /**
         * @author: LX
         * @methodName: getHighwayCongestionMap
         * @description: 获取最新的公路拥挤图，拥挤度小于等于ignoreValue不显示
         * @data: 2019-12-2
         */
        String sql = "SELECT geom, update_time, congest, update_time from main_highway_cn where congest > " + ignoreValue +
                " and update_time is not null";
        //String sql = "SELECT geom FROM highway_class_freeway WHERE name = '" + highwayName + "' AND " + distance + " = (SELECT MIN(" + distance + ") FROM highway_class_freeway WHERE name = '" + highwayName + "')";
        //TODO:确认字段名
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static String insertHighwayLocation(String highway_name, String highway_code, String seg_name, String seg_id, String geom, String update_time, Double speed, Double congest) throws SQLException {
        /**
         * @author: LX
         * @methodName: insertHighwayLocation
         * @description: 插入高速路段坐标
         * @data: 2019-11-15
         */
        int insertNum = 0;
        String sql = "insert into main_highway_cn ( highway_name, highway_code, seg_name, seg_id, geom, update_time, speed, congest )" +
                "values('" + highway_name + "', '" + highway_code + "', '" +
                seg_name + "', '" + seg_id +
                "'," + geom + ", '" + update_time +"', " + speed + ", " + congest + ");\n";
        Statement stmt = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
            //System.out.print(insertNum);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stmt.close();
        }
        return sql;
    }
    public static ResultSet getHighwayPoint(){
        /**
         * @author: LX
         * @methodName: getHighwayPoint
         * @description: 获取低精度路网
         * @data: 2019-12-4
         */
        String sql = "SELECT * FROM highway_rough_point";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
}
